"""
1.数据库类相关操作：
    1.查询(一条、多条、所有)
    2.统计(字段中的特定值)
2.数据库相关配置
3.实例化数据库对象
"""
from contextlib import contextmanager
import pymysql


class Database:
    # **config是指连接数据库时需要的参数,这样只要参数传入正确，连哪个数据库都可以
    # 初始化时就连接数据库
    def __init__(self, config):
        # 连接数据库的参数我不希望别人可以动，所以设置私有
        self._config = config
        self.__conn = pymysql.connect(**self._config)
        self.__cursor = self.__conn.cursor()

    @contextmanager
    def auto_reconnect(self):
        try:
            # 如果连接已断开，则重新连接
            self.__conn.ping(reconnect=True)
        except:
            self.__conn = pymysql.connect(**self._config)
        yield

    """
    插入
    """

    def insert(self, table_name, data):
        keys = ",".join(data.keys())
        values = ",".join(["%s"] * len(data))

        sql = "insert into %s (%s) values (%s)" % (table_name, keys, values)
        with self.auto_reconnect():
            self.__cursor.execute(sql, tuple(data.values()))

    """
    查找
    """

    def select_all(self, table_name):
        sql = f"select * from {table_name}"

        with self.auto_reconnect():
            self.__cursor.execute(sql)
        return self.__cursor.fetchall()

    # 查找条件为一个的
    def select_one_all(self, table_name, value, factor_str, field="*"):
        if field == '':
            sql = f"select {field} from {table_name}"
        else:
            sql = f"select {field} from {table_name} where {factor_str} = '{value}'"
        with self.auto_reconnect():
            self.__cursor.execute(sql)
        return self.__cursor.fetchall()

    # 查找条件为两个的
    def select_two_all(self, table_name, factor_str1, factor_str2, value1, value2, field="*"):
        if field == '':
            sql = f"select {field} from {table_name}"
        else:
            sql = f"select {field} from {table_name} where {factor_str1} = '{value1}' and {factor_str2} = '{value2}'"
        with self.auto_reconnect():
            self.__cursor.execute(sql)
        return self.__cursor.fetchall()

    # 查找条件为三个的
    def select_three_all(self, table_name, factor_str1, factor_str2, value1, value2, factor_str3, value3, field="*"):
        if field == '':
            sql = f"select {field} from {table_name}"
        else:
            sql = f"select {field} from {table_name} where {factor_str1} = '{value1}' " \
                  f"and {factor_str2} = '{value2}' and {factor_str3} = '{value3}'"
        with self.auto_reconnect():
            self.__cursor.execute(sql)
        return self.__cursor.fetchall()

    """
    统计
    """

    # 统计字段的不同值个数
    def count_field(self, table_name, field, value):
        sql = f"select sum(case when mrs.{field}='{value}' then 1 else 0 end) res from {table_name} mrs "
        with self.auto_reconnect():
            self.__cursor.execute(sql)
        count = self.__cursor.fetchall()[0]["res"]
        return int(count)

    """
    排序
    """

    # 排序
    def order(self, table_name, field, sort=False, value='', factor_str=''):
        if sort:
            sql = f"select * from {table_name} where {factor_str} = '{value}' order by {field} "
        else:
            sql = f"select * from {table_name} where {factor_str} = '{value}' order by {field} DESC "

        with self.auto_reconnect():
            self.__cursor.execute(sql)
        return self.__cursor.fetchall()

    def order_two(self, table_name, field, sort=False, factor_str1='', value1='', factor_str2='', value2=''):
        if sort:
            sql = f"select * from {table_name} where {factor_str1} = '{value1}' and {factor_str2} = {value2} order by {field} "
        else:
            sql = f"select * from {table_name} where {factor_str1} = '{value1}' and {factor_str2} = {value2} order by {field} DESC "

        with self.auto_reconnect():
            self.__cursor.execute(sql)
        return self.__cursor.fetchall()

    def order_select_three_all(self, table_name, factor_str1, factor_str2, value1, value2, factor_str3, value3, field):
        sql = f"select * from {table_name} where {factor_str1} = '{value1}' " \
              f"and {factor_str2} = '{value2}' and {factor_str3} = '{value3}' order by {field} DESC "
        with self.auto_reconnect():
            self.__cursor.execute(sql)
        return self.__cursor.fetchall()


# 数据库相关配置
yeuyan_config = {
    "host": "39.106.136.233",
    "port": 3306,
    "user": 'Yueyan',
    "passwd": 'swust66_root',
    "db": 'Yueyan',
    "autocommit": True,
    "cursorclass": pymysql.cursors.DictCursor,
    # "client_flag":CLIENT.MULTI_STATEMENTS
}


# 实例化数据库对象
def yueyan_database():
    yeuyan_database = Database(yeuyan_config)
    return yeuyan_database
